package com.qingke.oms.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.qingke.oms.pojo.Goods;
import com.qingke.oms.pojo.GoodsRecord;
import com.qingke.oms.pojo.GoodsType;
import com.qingke.oms.pojo.ItemQuantity;
import com.qingke.oms.pojo.Manufacturer;
import com.qingke.oms.pojo.Order;
import com.qingke.oms.pojo.OrderItem;
import com.qingke.oms.pojo.Shop;
import com.qingke.oms.pojo.ShoppingCart;
import com.qingke.oms.pojo.ShoppingCartItem;
import com.qingke.oms.pojo.User;


public class OrderManagementSystemDAO extends OrderManagementSystemBottomDAO{
	
	public User login(String username,String password){
		List<User> arr = selectUser(-1,null,username,password,Double.MIN_VALUE);
		User user=null;
		if(arr.size()>0){
			user=arr.get(0);
		}
		return user;
	}


	public boolean register(String name,String username,String password){
		int isTrue = insertUser(name, username, password);
		if(isTrue>0)
			return true;
		else
			return false;
	}
	
	public boolean isUsernameExists(String username){
		List<User> arr = selectUser(-1,null,username,null,Double.MIN_VALUE);
		if(arr.size()>0)
			return true;
		else
			return false;
	}
	
	public boolean registerShop(int userId,String name,String description){
		sql="insert into shop(owner_id,name,description) values(?,?,?);";
		int isSuccess=0;
		try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1,userId);
			ps.setString(2,name);
			ps.setString(3,description);
			isSuccess=ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			cleanup(ps);
		}
		if(isSuccess>0)
			return true;
		else
			return false;
	}
	
	public User selectUserById(int id){
		User user=null;
		sql="select * from user where id=?;";
		try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1,id);
			rs=ps.executeQuery();
			if(rs!=null){
				while(rs.next()){
					user=new User();
					user.setId(id);
					user.setName(rs.getString("name"));
					user.setBalance(rs.getDouble("balance"));
					break;
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			cleanup(rs);
			cleanup(ps);
		}
		return user;
	}
	
	public synchronized boolean updateUserBalanceToShopTurnover(int userId,int shopId,double money){
		int count=0;
		User user=selectUserById(userId);
		Shop shop=selectShopById(shopId);
		if(user==null||shop==null)
			return false;
		if(money>user.getBalance())
			return false;
		
		try {
			conn.setAutoCommit(false);
			sql="update user set balance=? where id=?;";
			ps=conn.prepareStatement(sql);
			ps.setDouble(1, user.getBalance()-money);
			ps.setInt(2,userId);
			count+=ps.executeUpdate();
			sql="update shop set turnover=? where id=?;";
			ps=conn.prepareStatement(sql);
			ps.setDouble(1, shop.getTurnover()+money);
			ps.setInt(2,shopId);
			count+=ps.executeUpdate();
			conn.commit();
			conn.setAutoCommit(true);
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} finally {
			cleanup(ps);
		}
		if(count==2)
			return true;
		else
			return false;
	}
	
	public synchronized boolean updateUserBalanceRecharge(int userId,double money){
		User user=selectUserById(userId);
		int isSuccess=0;
		sql="update user set balance=? where id=?";
		try {
			ps=conn.prepareStatement(sql);
			ps.setDouble(1,user.getBalance()+money);
			ps.setInt(2,user.getId());
			isSuccess=ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			cleanup(ps);
		}
		if(isSuccess>0)
			return true;
		else
			return false;
	}
	
	public boolean insertGoodsType(String code,String name,String description){
		sql="insert into goods_type(code,name,description) values(?,?,?);";
		int isSuccess=0;
		try {
			ps=conn.prepareStatement(sql);
			ps.setString(1,code);
			ps.setString(2,name);
			ps.setString(3,description);
			isSuccess=ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			cleanup(ps);
		}
		if(isSuccess>0)
			return true;
		else
			return false;
	}
	
	public List<GoodsType> selectAllGoodsType(){
		List<GoodsType> list = new ArrayList<>();
		sql="select * from goods_type;";
		try {
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			if(rs!=null){
				while(rs.next()){
					GoodsType gt=new GoodsType();
					gt.setId(rs.getInt("id"));
					gt.setCode(rs.getString("code"));
					gt.setName(rs.getString("name"));
					gt.setDescription(rs.getString("description"));
					list.add(gt);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			cleanup(rs);
			cleanup(ps);
		}
		
		return list;
	}
	
	public GoodsType selectGoodsTypeById(int id){
		GoodsType goodsType=null;
		sql="select * from goods_type where id=?;";
		try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1,id);
			rs=ps.executeQuery();
			if(rs!=null){
				while(rs.next()){
					goodsType=new GoodsType();
					goodsType.setId(id);
					goodsType.setCode(rs.getString("code"));
					goodsType.setName(rs.getString("name"));
					goodsType.setDescription(rs.getString("description"));
					break;
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			cleanup(rs);
			cleanup(ps);
		}
		
		return goodsType;
	}
	
	public GoodsType selectGoodsTypeByCode(String code){
		GoodsType goodsType=null;
		sql="select * from goods_type where code=?;";
		try {
			ps=conn.prepareStatement(sql);
			ps.setString(1,code);
			rs=ps.executeQuery();
			if(rs!=null){
				while(rs.next()){
					goodsType=new GoodsType();
					goodsType.setId(rs.getInt("id"));
					goodsType.setCode(code);
					goodsType.setName(rs.getString("name"));
					goodsType.setDescription(rs.getString("description"));
					break;
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			cleanup(rs);
			cleanup(ps);
		}
		
		return goodsType;
	}
	
	public boolean isGoodsTypeExistByCode(String code){
		if(selectGoodsTypeByCode(code)==null)
			return false;
		else
			return true;
	}
	
	public boolean insertManufacturer(String code,String name,String address,String description){
		sql="insert into manufacturer(code,name,address,description) values(?,?,?,?);";
		int isSuccess=0;
		try {
			ps=conn.prepareStatement(sql);
			ps.setString(1,code);
			ps.setString(2,name);
			ps.setString(3,address);
			ps.setString(4,description);
			isSuccess=ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			cleanup(ps);
		}
		if(isSuccess>0)
			return true;
		else
			return false;
	}
	
	public List<Manufacturer> selectAllManufacturer(){
		List<Manufacturer> list = new ArrayList<>();
		sql="select * from manufacturer;";
		try {
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			if(rs!=null){
				while(rs.next()){
					Manufacturer m=new Manufacturer();
					m.setId(rs.getInt("id"));
					m.setCode(rs.getString("code"));
					m.setName(rs.getString("name"));
					m.setName(rs.getString("address"));
					m.setDescription(rs.getString("description"));
					list.add(m);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			cleanup(rs);
			cleanup(ps);
		}
		
		return list;
	}
	
	public Manufacturer selectManufacturerById(int id){
		Manufacturer manufacturer=null;
		sql="select * from manufacturer where id=?;";
		try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1,id);
			rs=ps.executeQuery();
			if(rs!=null){
				while(rs.next()){
					manufacturer=new Manufacturer();
					manufacturer.setId(id);
					manufacturer.setCode(rs.getString("code"));
					manufacturer.setName(rs.getString("name"));
					manufacturer.setName(rs.getString("address"));
					manufacturer.setDescription(rs.getString("description"));
					break;
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			cleanup(rs);
			cleanup(ps);
		}
		
		return manufacturer;
	}
	
	public Manufacturer selectManufacturerByCode(String code){
		Manufacturer manufacturer=null;
		sql="select * from manufacturer where code=?;";
		try {
			ps=conn.prepareStatement(sql);
			ps.setString(1,code);
			rs=ps.executeQuery();
			if(rs!=null){
				while(rs.next()){
					manufacturer=new Manufacturer();
					manufacturer.setId(rs.getInt("id"));
					manufacturer.setCode(code);
					manufacturer.setName(rs.getString("name"));
					manufacturer.setName(rs.getString("address"));
					manufacturer.setDescription(rs.getString("description"));
					break;
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			cleanup(rs);
			cleanup(ps);
		}
		
		return manufacturer;
	}
	
	public boolean isManufacturerExistByCode(String code){
		if(selectManufacturerByCode(code)==null)
			return false;
		else
			return true;
	}
	
	public boolean insertGoods(String code,String name,int goodsTypeId,int manufacturer,String description){
		sql="insert into goods(code,name,goods_type_id,manufacturer_id,description) values(?,?,?,?,?);";
//		PreparedStatement ps=null;
		int isSuccess=0;
		try {
			ps=conn.prepareStatement(sql);
			ps.setString(1,code);
			ps.setString(2,name);
			ps.setInt(3,goodsTypeId);
			ps.setInt(4,manufacturer);
			ps.setString(5,description);
			isSuccess=ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			cleanup(ps);
		}
		if(isSuccess>0)
			return true;
		else
			return false;
	}
	
	public List<Goods> selectAllGoods(){
		List<Goods> list = new ArrayList<>();
		sql="select * from goods where be_deleted=false;";
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			if(rs!=null){
				while(rs.next()){
					Goods g=new Goods();
					g.setId(rs.getInt("id"));
					g.setCode(rs.getString("code"));
					g.setCode(rs.getString("name"));
					g.setGoodsType(selectGoodsTypeById(rs.getInt("goods_type_id")));
					g.setManufacturer(selectManufacturerById(rs.getInt("manufacturer_id")));
					g.setCode(rs.getString("description"));
					list.add(g);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			cleanup(rs);
			cleanup(ps);
		}
		return list;
	}
	
	public List<Goods> selectGoodsByGoodsType(int goodsTypeId){
		List<Goods> list = new ArrayList<>();
		sql="select * from goods where goods_type_id=? and be_deleted=false;";
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1,goodsTypeId);
			rs=ps.executeQuery();
			if(rs!=null){
				while(rs.next()){
					Goods g=new Goods();
					g.setId(rs.getInt("id"));
					g.setCode(rs.getString("code"));
					g.setCode(rs.getString("name"));
					g.setGoodsType(selectGoodsTypeById(rs.getInt("goods_type_id")));
					g.setManufacturer(selectManufacturerById(rs.getInt("manufacturer_id")));
					g.setCode(rs.getString("description"));
					list.add(g);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			cleanup(rs);
			cleanup(ps);
		}
		return list;
	}
	
	public List<Goods> selectGoodsByName(String name){
		List<Goods> list = new ArrayList<>();
		sql="select * from goods where name=? and be_deleted=false;";
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			ps=conn.prepareStatement(sql);
			ps.setString(1,name);
			rs=ps.executeQuery();
			if(rs!=null){
				while(rs.next()){
					Goods g=new Goods();
					g.setId(rs.getInt("id"));
					g.setCode(rs.getString("code"));
					g.setCode(rs.getString("name"));
					g.setGoodsType(selectGoodsTypeById(rs.getInt("goods_type_id")));
					g.setManufacturer(selectManufacturerById(rs.getInt("manufacturer_id")));
					g.setCode(rs.getString("description"));
					list.add(g);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			cleanup(rs);
			cleanup(ps);
		}
		return list;
	}
	
	public List<Goods> selectGoodsByLikeCodeOrName(String key){
		List<Goods> list = new ArrayList<>();
		sql="select * from Goods where code like ? or name like ? and be_deleted=false;";
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			ps=conn.prepareStatement(sql);
			ps.setString(1,"%"+key+"%");
			ps.setString(2,"%"+key+"%");
			rs=ps.executeQuery();
			if(rs!=null){
				while(rs.next()){
					Goods g = new Goods();
					g.setId(rs.getInt("id"));
					g.setCode(rs.getString("code"));
					g.setName(rs.getString("name"));
					g.setGoodsType(selectGoodsTypeById(rs.getInt("goods_type_id")));
					g.setManufacturer(selectManufacturerById(rs.getInt("manufacturer_id")));
					g.setCode(rs.getString("description"));
					list.add(g);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			cleanup(rs);
			cleanup(ps);
		}
		return list;
	}
	
	public Goods selectGoodsById(int id){
		Goods goods=null;
		sql="select * from goods where id=? and be_deleted=false;";
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1,id);
			rs=ps.executeQuery();
			if(rs!=null){
				while(rs.next()){
					goods=new Goods();
					goods.setId(rs.getInt("id"));
					goods.setCode(rs.getString("code"));
					goods.setName(rs.getString("name"));
					goods.setGoodsType(selectGoodsTypeById(rs.getInt("goods_type_id")));
					goods.setManufacturer(selectManufacturerById(rs.getInt("manufacturer_id")));
					goods.setCode(rs.getString("description"));
					break;
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			cleanup(rs);
			cleanup(ps);
		}
		return goods;
	}
	
	public boolean isGoodsExistById(int id){
		if(selectGoodsById(id)==null)
			return false;
		else
			return true;
	}
	
	public Goods selectGoodsByCode(String code){
		Goods goods=null;
		sql="select * from goods where code=? and be_deleted=false;";
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			ps=conn.prepareStatement(sql);
			ps.setString(1,code);
			rs=ps.executeQuery();
			if(rs!=null){
				while(rs.next()){
					goods=new Goods();
					goods.setId(rs.getInt("id"));
					goods.setCode(rs.getString("code"));
					goods.setCode(rs.getString("name"));
					goods.setGoodsType(selectGoodsTypeById(rs.getInt("goods_type_id")));
					goods.setManufacturer(selectManufacturerById(rs.getInt("manufacturer_id")));
					goods.setCode(rs.getString("description"));
					break;
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			cleanup(rs);
			cleanup(ps);
		}
		return goods;
	}
	
	public boolean isGoodsExistByCode(String code){
		if(selectGoodsByCode(code)==null)
			return false;
		else
			return true;
	}
	
	public List<Shop> selectAllShop(){
		List<Shop> list = new ArrayList<>();
		sql="select * from shop ;";
		try {
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			if(rs!=null){
				while(rs.next()){
					Shop shop = new Shop();
					shop.setId(rs.getInt("id"));
					shop.setOwnerId(rs.getInt("owner_id"));
					shop.setName(rs.getString("name"));
					shop.setTurnover(rs.getDouble("turnover"));
					shop.setDescription(rs.getString("description"));
					list.add(shop);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			cleanup(rs);
			cleanup(ps);
		}
		
		return list;
	}
	
	public List<Shop> selectShopByOwnerId(int ownerId){
		List<Shop> list = new ArrayList<>();
		sql="select * from shop where owner_id=?;";
		try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1,ownerId);
			rs=ps.executeQuery();
			if(rs!=null){
				while(rs.next()){
					Shop shop = new Shop();
					shop.setId(rs.getInt("id"));
					shop.setOwnerId(rs.getInt("owner_id"));
					shop.setName(rs.getString("name"));
					shop.setTurnover(rs.getDouble("turnover"));
					shop.setDescription(rs.getString("description"));
					list.add(shop);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			cleanup(rs);
			cleanup(ps);
		}
		
		return list;
	}
	
	public Shop selectShopById(int id){
		Shop shop=null;
		sql="select * from shop where id=?;";
		try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1,id);
			rs=ps.executeQuery();
			if(rs!=null){
				while(rs.next()){
					shop = new Shop();
					shop.setId(rs.getInt("id"));
					shop.setOwnerId(rs.getInt("owner_id"));
					shop.setName(rs.getString("name"));
					shop.setTurnover(rs.getDouble("turnover"));
					shop.setDescription(rs.getString("description"));
					break;
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			cleanup(rs);
			cleanup(ps);
		}
		
		return shop;
	}
	
	public boolean isShopExistById(int id){
		if(selectShopById(id)==null)
			return false;
		else
			return true;
	}
	
	public synchronized boolean updateShopTurnoverToUserBalance(int shopId,int userId,double money){
		int count=0;
		User user=selectUserById(userId);
		Shop shop=selectShopById(shopId);
		if(user==null||shop==null)
			return false;
		if(money>shop.getTurnover())
			return false;
		
		try {
			conn.setAutoCommit(false);
			sql="update shop set turnover=? where id=?;";
			ps=conn.prepareStatement(sql);
			ps.setDouble(1, shop.getTurnover()-money);
			ps.setInt(2,shopId);
			count+=ps.executeUpdate();
			sql="update user set balance=? where id=?;";
			ps=conn.prepareStatement(sql);
			ps.setDouble(1, user.getBalance()+money);
			ps.setInt(2,userId);
			count+=ps.executeUpdate();
			conn.commit();
			conn.setAutoCommit(true);
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} finally {
			cleanup(ps);
		}
		if(count==2)
			return true;
		else
			return false;
	}
	
	public boolean insertShelfRecord(int shopId,int goodsId,int quantity,int shelfStatusId){
		sql="insert into shelf_record(shop_id,goods_id,quantity,shelf_status_id) values(?,?,?,?);";
		int isSuccess=0;
		try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1,shopId);
			ps.setInt(2,goodsId);
			ps.setInt(3,quantity);
			ps.setInt(4,shelfStatusId);
			isSuccess=ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			cleanup(ps);
		}
		if(isSuccess>0)
			return true;
		else
			return false;
			
	}
	
	public int getShelfStatusIdByStatus(String status){
		sql="select * from shelf_status where status=?";
		int id=-1;
		try {
			ps=conn.prepareStatement(sql);
			ps.setString(1,status);
			rs=ps.executeQuery();
			if(rs!=null){
				while(rs.next()){
					id=rs.getInt("id");
					break;
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			cleanup(rs);
			cleanup(rs);
		}
		return id;
		
	}
	
	public List<GoodsRecord> selectCountShelfRecordByShelfStatus(String status){
		int shelfStatusId=getShelfStatusIdByStatus(status);
		sql="select shop_id,goods_id,sum(quantity) total from shelf_record where shelf_status_id=? group by shop_id,goods_id;";
		List<GoodsRecord> list = new ArrayList<>();
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1,shelfStatusId);
			rs=ps.executeQuery();
			if(rs!=null){
				while(rs.next()){
					GoodsRecord gr=new GoodsRecord();
					gr.setShopId(rs.getInt("shop_id"));
					gr.setGoods(selectGoodsById(rs.getInt("goods_id")));
					gr.setQuantity(rs.getInt("total"));
					list.add(gr);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			cleanup(rs);
			cleanup(ps);
		}
		return list;
	}
	
	public int getShipmentStatusIdByStatus(String status){
		sql="select * from shipment_status where status=?";
		int id=-1;
		try {
			ps=conn.prepareStatement(sql);
			ps.setString(1,status);
			rs=ps.executeQuery();
			if(rs!=null){
				while(rs.next()){
					id=rs.getInt("id");
					break;
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			cleanup(rs);
			cleanup(rs);
		}
		return id;
		
	}
	
	public List<GoodsRecord> selectCountShipmentRecordByShelfStatus(String status){
		int shipmentStatusId=getShipmentStatusIdByStatus(status);
		sql="select shop_id,goods_id,sum(quantity) total from shipment_record where shipment_status_id=? group by shop_id,goods_id;";
		List<GoodsRecord> list = new ArrayList<>();
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1,shipmentStatusId);
			rs=ps.executeQuery();
			if(rs!=null){
				while(rs.next()){
					GoodsRecord gr=new GoodsRecord();
					gr.setShopId(rs.getInt("shop_id"));
					gr.setGoods(selectGoodsById(rs.getInt("goods_id")));
					gr.setQuantity(rs.getInt("total"));
					list.add(gr);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	public boolean isGoodsStatisticExistByShopIdAndGoodsId(int shopId,int goodsId){
		sql="select * from goods_statistic where shop_id=? and goods_id=?";
		int isExist=0;
		try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1,shopId);
			ps.setInt(2,goodsId);
			rs=ps.executeQuery();
			if(rs!=null){
				while(rs.next()){
					isExist=1;
					break;
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			cleanup(rs);
			cleanup(ps);
		}
		if(isExist>0)
			return true;
		else
			return false;
	}
	
	public boolean insertGoodsStatistic(int shopId,int goodsId,int stock){
		sql="insert into goods_statistic(shop_id,goods_id,stock) values(?,?,?);";
		int isSuccess=0;
		try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1,shopId);
			ps.setInt(2,goodsId);
			ps.setInt(3,stock);
			isSuccess=ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			cleanup(ps);
		}
		if(isSuccess>0)
			return true;
		else
			return false;
		
	}
	
	public boolean updateGoodsStatisticStockByShopIdAndGoodsId(int stock,int shopId,int goodsId){
		sql="update goods_statistic set stock=? where shop_id=? and goods_id=?;";
		int isSuccess=0;
		try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1,stock);
			ps.setInt(2,shopId);
			ps.setInt(3,goodsId);
			isSuccess=ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			cleanup(ps);
		}
		if(isSuccess>0)
			return true;
		else
			return false;
	}
	
	public boolean updateGoodsStatisticPriceByShopIdAndGoodsId(double price,int shopId,int goodsId){
		sql="update goods_statistic set price=? where shop_id=? and goods_id=?;";
		int isSuccess=0;
		try {
			ps=conn.prepareStatement(sql);
			ps.setDouble(1,price);
			ps.setInt(2,shopId);
			ps.setInt(3,goodsId);
			isSuccess=ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			cleanup(ps);
		}
		if(isSuccess>0)
			return true;
		else
			return false;
	}
	
	public GoodsRecord selectGoodsStatisticByShopIdAndGoodsId(int shopId,int goodsId){
		sql="select * from goods_statistic where shop_id=? and goods_id=?";
		GoodsRecord goodsRecord=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1,shopId);
			ps.setInt(2,goodsId);
			rs=ps.executeQuery();
			if(rs!=null){
				while(rs.next()){
					goodsRecord=new GoodsRecord();
					goodsRecord.setShopId(shopId);
					goodsRecord.setGoods(selectGoodsById(rs.getInt("goods_id")));
					goodsRecord.setQuantity(rs.getInt("stock"));
					goodsRecord.setPrice(rs.getDouble("price"));
					break;
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			cleanup(rs);
			cleanup(ps);
		}
		return goodsRecord;
	}
	
	public List<GoodsRecord> selectGoodsStatisticByShopId(int shopId){
		List<GoodsRecord> list = new ArrayList<>();
		sql="select shop_id,goods_id,stock,price from goods_statistic where shop_id=?;";
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1,shopId);
			rs=ps.executeQuery();
			if(rs!=null){
				while(rs.next()){
					GoodsRecord gr=new GoodsRecord();
					gr.setShopId(rs.getInt("shop_id"));
					gr.setGoods(selectGoodsById(rs.getInt("goods_id")));
					gr.setQuantity(rs.getInt("stock"));
					gr.setPrice(rs.getDouble("price"));
					list.add(gr);
				}
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			cleanup(rs);
			cleanup(ps);
		}
		return list;
	}
	
	public ShoppingCart selectShoppingCart(int customerId,int shopId){
		sql="select * from shopping_cart where customer_id=? and shop_id=?;";
		ShoppingCart shoppingCart=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1,customerId);
			ps.setInt(2,shopId);
			rs=ps.executeQuery();
			if(rs!=null){
				while(rs.next()){
					shoppingCart=new ShoppingCart();
					shoppingCart.setId(rs.getInt("id"));
					shoppingCart.setCustomerId(rs.getInt("customer_id"));
					shoppingCart.setShopId(rs.getInt("shop_id"));
					shoppingCart.setItems(selectShoppingCartItemByShoppingCartId(shoppingCart.getId()));
					shoppingCart.setTotal(rs.getDouble("total_price"));
					break;
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			cleanup(rs);
			cleanup(ps);
		}
		return shoppingCart;
	}
	
	public boolean isShoppingCartExistByCustomerIdAndShopId(int customerId,int shopId){
		ShoppingCart shoppingcart=selectShoppingCart(customerId, shopId);
		if(shoppingcart==null)
			return false;
		else
			return true;
	}
	
	public boolean insertShoppingCart(int customerId,int shopId){
		sql="insert into shopping_cart(customer_id,shop_id) values(?,?);";
		int isSuccess=0;
		try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1,customerId);
			ps.setInt(2,shopId);
			isSuccess=ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			cleanup(ps);
		}
		if(isSuccess>0)
			return true;
		else
			return false;
	}
	
	public boolean updateShoppingCart(double total,int customerId,int shopId){
		sql="update shopping_cart set total_price=? where customer_id=? and shop_id=?;";
		int isSuccess=0;
		try {
			ps=conn.prepareStatement(sql);
			ps.setDouble(1,total);
			ps.setInt(2,customerId);
			ps.setInt(3,shopId);
			isSuccess=ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			cleanup(ps);
		}
		if(isSuccess>0)
			return true;
		else
			return false;
	}
	
	public boolean insertShoppingCartItem(int shoppingCartId,int goodsId,double price){
		sql="insert into shopping_cart_item(shopping_cart_id,goods_id,price) values(?,?,?);";
		int isSuccess=0;
		try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1,shoppingCartId);
			ps.setInt(2,goodsId);
			ps.setDouble(3,price);
			isSuccess=ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			cleanup(ps);
		}
		if(isSuccess>0)
			return true;
		else
			return false;
	}
	
	public List<ShoppingCartItem> selectShoppingCartItemByShoppingCartId(int shoppingCartId){
		sql="select * from shopping_cart_item where shopping_cart_id=? and be_cleared=false;";
		List<ShoppingCartItem> items=new ArrayList<>();
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1,shoppingCartId);
			rs=ps.executeQuery();
			if(rs!=null){
				while(rs.next()){
					ShoppingCartItem item = new ShoppingCartItem();
					item.setId(rs.getInt("id"));
					item.setShoppingCartId(rs.getInt("shopping_cart_id"));
					item.setGoods(selectGoodsById(rs.getInt("goods_id")));
					item.setPrice(rs.getDouble("price"));
					ItemQuantity iq=selectItemQuantityByShoppingCartItemId(rs.getInt("id"));
					if(iq!=null)
						item.setQuantity(iq.getQuantity());
					else
						item.setQuantity(0);
					if(item.getQuantity()>0)
						items.add(item);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			cleanup(rs);
			cleanup(ps);
		}
		return items;
	}
	
	public ShoppingCartItem selectShoppingCartItemByShoppingCartIdAndGoodsId(int shoppingCartId,int goodsId){
		sql="select * from shopping_cart_item where shopping_cart_id=? and goods_id=? and be_cleared=false;";
		ShoppingCartItem item=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1,shoppingCartId);
			ps.setInt(2,goodsId);
			rs=ps.executeQuery();
			if(rs!=null){
				while(rs.next()){
					item = new ShoppingCartItem();
					item.setId(rs.getInt("id"));
					item.setShoppingCartId(rs.getInt("shopping_cart_id"));
					item.setGoods(selectGoodsById(rs.getInt("goods_id")));
					item.setPrice(rs.getDouble("price"));
					ItemQuantity iq=selectItemQuantityByShoppingCartItemId(rs.getInt("id"));
					if(iq!=null)
						item.setQuantity(iq.getQuantity());
					else
						item.setQuantity(0);
					break;
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			cleanup(rs);
			cleanup(ps);
		}
		return item;
	}
	
	public boolean updateShoppingCartItemBeCleared(ShoppingCart shoppingCart){
		sql="update shopping_cart_item set be_cleared=true where shopping_cart_id=?;";
		int isSuccess=0;
		try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1,shoppingCart.getId());
			isSuccess=ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			cleanup(ps);
		}
		if(isSuccess==shoppingCart.getItems().size())
			return true;
		else
			return false;
	}
	
	public boolean isShoppingCartItemExistByShoppingCartIdAndGoodsId(int shoppingCartId,int goodsId){
		ShoppingCartItem shoppingCartItem=selectShoppingCartItemByShoppingCartIdAndGoodsId(shoppingCartId,goodsId);
		if(shoppingCartItem==null)
			return false;
		else
			return true;
	}
		
	public ItemQuantity selectItemQuantityByShoppingCartItemId(int shoppingCartItemId){
		sql="select * from item_quantity where shopping_cart_item_id=? order by modification_time desc;";
		ItemQuantity itemQuantity=null;
		try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1,shoppingCartItemId);
			rs=ps.executeQuery();
			if(rs!=null){
				while(rs.next()){
					itemQuantity=new ItemQuantity();
					itemQuantity.setId(rs.getInt("id"));
					itemQuantity.setShoppingCartItemId(rs.getInt("shopping_cart_item_id"));
					itemQuantity.setQuantity(rs.getInt("quantity"));
					break;
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			cleanup(rs);
			cleanup(ps);
		}
		return itemQuantity;
		
	}
	
	public boolean insertItemQuantity(int shoppingCartItemId,int quantity){
		sql="insert into item_quantity(shopping_cart_item_id,quantity) values(?,?);";
		int isSuccess=0;
		try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1,shoppingCartItemId);
			ps.setInt(2,quantity);
			isSuccess=ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			cleanup(ps);
		}
		if(isSuccess>0)
			return true;
		else
			return false;
		
	}
	
	public boolean insertOrder(ShoppingCart shoppingCart){
		sql="insert into `order`(customer_id,shop_id,total) values(?,?,?);";
		int isSuccess=0;
		try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1,shoppingCart.getCustomerId());
			ps.setInt(2,shoppingCart.getShopId());
			ps.setDouble(3,shoppingCart.getTotal());
			isSuccess=ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			cleanup(ps);
		}
		if(isSuccess>0)
			return true;
		else
			return false;
	}
	public boolean insertOrderItems(ShoppingCart shoppingCart){
		int count=0;
		Order order=selectOrderNewestByCustomerIdAndShopId(shoppingCart.getCustomerId(), shoppingCart.getShopId());
		if(order==null){
			return false;
		}
		int orderId=order.getId();
		List<ShoppingCartItem> items=shoppingCart.getItems();
		
		sql="insert into order_item(order_id,goods_id,price,quantity) values(?,?,?,?);";
		try {
			conn.setAutoCommit(false);
			for(ShoppingCartItem i:items){
				ps=conn.prepareStatement(sql);
				ps.setInt(1,orderId);
				ps.setInt(2,i.getGoods().getId());
				ps.setDouble(3,i.getPrice());
				ps.setInt(4,i.getQuantity());
				count+=ps.executeUpdate();
			}
			
			conn.commit();
			conn.setAutoCommit(true);
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} finally {
			cleanup(ps);
		}
		if(count==items.size())
			return true;
		else
			return false;
	}
	
	public Order selectOrderNewestByCustomerIdAndShopId(int customerId,int shopId){
		Order order=null;
		sql="select * from `order` where customer_id=? and shop_id=? and be_deleted=false order by create_time desc limit 1;";
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1,customerId);
			ps.setInt(2,shopId);
			rs=ps.executeQuery();
			if(rs!=null){
				while(rs.next()){
					order=new Order();
					order.setId(rs.getInt("id"));
					order.setCustomerId(rs.getInt("customer_id"));
					order.setShopId(rs.getInt("shop_id"));
					order.setItems(selectOrderItemByOrderId(order.getId()));
					order.setTotal(rs.getDouble("total"));
					order.setCreateTime(rs.getDate("create_time"));
					break;
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			cleanup(rs);
			cleanup(ps);
		}
		return order;
	}
	
	public Order selectOrderById(int id){
		Order order=null;
		sql="select * from `order` where id=? and be_deleted=false;";
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1,id);
			rs=ps.executeQuery();
			if(rs!=null){
				while(rs.next()){
					order=new Order();
					order.setId(rs.getInt("id"));
					order.setCustomerId(rs.getInt("customer_id"));
					order.setShopId(rs.getInt("shop_id"));
					order.setItems(selectOrderItemByOrderId(order.getId()));
					order.setTotal(rs.getDouble("total"));
					order.setCreateTime(rs.getDate("create_time"));
					break;
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			cleanup(rs);
			cleanup(ps);
		}
		return order;
	}
	
	public List<Order> selectOrderByShopId(int shopId){
		List<Order> list=new ArrayList<>();
		sql="select * from `order` where shop_id=? and be_deleted=false;";
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1,shopId);
			rs=ps.executeQuery();
			if(rs!=null){
				while(rs.next()){
					Order order=new Order();
					order.setId(rs.getInt("id"));
					order.setCustomerId(rs.getInt("customer_id"));
					order.setShopId(rs.getInt("shop_id"));
					order.setItems(selectOrderItemByOrderId(order.getId()));
					order.setTotal(rs.getDouble("total"));
					order.setCreateTime(rs.getDate("create_time"));
					list.add(order);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			cleanup(rs);
			cleanup(ps);
		}
		return list;
	}
	
	public List<Order> selectOrderByShopIdAndStatus(int shopId,String statusCode){
		List<Order> list=new ArrayList<>();
		sql="select o.* from `order` o,transaction_status_record tsr,order_status os where shop_id=? and be_deleted=false and tsr.order_id=o.id and tsr.order_status_id=os.id and os.code=?;";
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1,shopId);
			ps.setString(2,statusCode);
			rs=ps.executeQuery();
			if(rs!=null){
				while(rs.next()){
					Order order=new Order();
					order.setId(rs.getInt("id"));
					order.setCustomerId(rs.getInt("customer_id"));
					order.setShopId(rs.getInt("shop_id"));
					order.setItems(selectOrderItemByOrderId(order.getId()));
					order.setTotal(rs.getDouble("total"));
					order.setCreateTime(rs.getDate("create_time"));
					list.add(order);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			cleanup(rs);
			cleanup(ps);
		}
		return list;
	}
	
	public List<Order> selectOrderByUserIdAndStatus(int userId,String statusCode){
		List<Order> list=new ArrayList<>();
		sql="select o.*,max(tsr.modification_time) from `order` o,transaction_status_record tsr,order_status os where o.customer_id=? and o.be_deleted=false and tsr.order_id=o.id and tsr.order_status_id=os.id and os.code=? group by o.id;";
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1,userId);
			ps.setString(2,statusCode);
			rs=ps.executeQuery();
			if(rs!=null){
				while(rs.next()){
					Order order=new Order();
					order.setId(rs.getInt("id"));
					order.setCustomerId(rs.getInt("customer_id"));
					order.setShopId(rs.getInt("shop_id"));
					order.setItems(selectOrderItemByOrderId(order.getId()));
					order.setTotal(rs.getDouble("total"));
					order.setCreateTime(rs.getDate("create_time"));
					list.add(order);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			cleanup(rs);
			cleanup(ps);
		}
		return list;
	}
	
	
	public List<OrderItem> selectOrderItemByOrderId(int orderId){
		List<OrderItem> items=new ArrayList<>();
		sql="select * from order_item where order_id=?;";
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1,orderId);
			rs=ps.executeQuery();
			if(rs!=null){
				while(rs.next()){
					OrderItem item=new OrderItem();
					item.setId(rs.getInt("id"));
					item.setOrderId(rs.getInt("order_id"));
					item.setGoods(selectGoodsById(rs.getInt("goods_id")));
					item.setPrice(rs.getDouble("price"));
					item.setQuantity(rs.getInt("quantity"));
					items.add(item);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			cleanup(rs);
			cleanup(ps);
		}
		return items;
	}
	
	public boolean insertTransactionStatusRecord(int orderId,String orderStatusCode){
		int orderStatusId=selectOrderStatusIdByCode(orderStatusCode);
		sql="insert into transaction_status_record(order_id,order_status_id) values(?,?);";
		int isSuccess=0;
		try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1,orderId);
			ps.setInt(2,orderStatusId);
			isSuccess=ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			cleanup(ps);
		}
		if(isSuccess>0)
			return true;
		else
			return false;
	}
	
	public boolean insertShipmentRecord(Order order,String status){
		int statusId=getShipmentStatusIdByStatus(status);
		List<OrderItem> items=order.getItems();
		int count=0;
		sql="insert into shipment_record(shop_id,goods_id,quantity,shipment_status_id,order_item_id) values(?,?,?,?,?);";
		try {
			conn.setAutoCommit(false);
			for(OrderItem i:items){
				ps=conn.prepareStatement(sql);
				ps.setInt(1,order.getShopId());
				ps.setInt(2,i.getGoods().getId());
				ps.setInt(3,i.getQuantity());
				ps.setInt(4,statusId);
				ps.setInt(5,i.getId());
				count+=ps.executeUpdate();
			}
			conn.commit();
			conn.setAutoCommit(true);
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		}
		
		if(count==items.size())
			return true;
		else
			return false;
	}
	
	public int selectOrderStatusIdByCode(String code){
		sql="select * from order_status where code=?;";
		int id=-1;
		try {
			ps=conn.prepareStatement(sql);
			ps.setString(1,code);
			rs=ps.executeQuery();
			if(rs!=null){
				while(rs.next()){
					id=rs.getInt("id");
					break;
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			cleanup(ps);
		}
		return id;
	}
}
